package edu.egypt.helpers;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import edu.egypt.model.Permission;
import edu.egypt.model.Role_Permission;
public class PermissionsDbOperations {

	private static PermissionsDbOperations instance = null;
	private static Connection conn= null;
	private static Statement stmt=null;
	private static ResultSet rs=null;
	private static PreparedStatement ps = null;
	private PermissionsDbOperations(){
		// making only constructor as private so that no one can get instance other than getInstance method
	}
	
	public static synchronized PermissionsDbOperations getSingletonInstance(){
		
		if( instance == null){
			instance = new PermissionsDbOperations();
		}
		
		return instance;
		
	}
	
	
public void update (int Rid,int Pid){
		
		try{
				ps= getDbConnection().prepareStatement("insert ignore into Role_permission (Role_ID,Permission_ID) values(?,?)");
				ps.setInt(1,Rid);
				ps.setInt(2,Pid);
			    ps.executeUpdate();
		}catch(SQLException ex){
			
		}
		
	}
	

public void update2 (int Rid,int Pid){
	
	try{
			ps= getDbConnection().prepareStatement("delete from Role_permission where Role_ID=? and Permission_ID=?");
			ps.setInt(1,Rid);
			ps.setInt(2,Pid);
		    ps.executeUpdate();
	}catch(SQLException ex){
		
	}
	
}

	public ArrayList<Permission> getAllPermissions(){
		
		ArrayList<Permission> result = new ArrayList<>();
		try{
			String sql = "SELECT * FROM permission";
			stmt= getDbConnection().createStatement();
			rs= stmt.executeQuery(sql);
			while(rs.next()){
	            String name = rs.getString("Name");
	            String desc = rs.getString("description");
	            int id  = rs.getInt("ID");
	            Permission p=new Permission(id,name,desc,"");
	            result.add(p);
	         }
			// get all permissions as resultset and then convert them into arrayist of permissions..
		}catch(SQLException ex){
			
		}
		
		
		return result;
		
	}
	
public ArrayList<Permission> getAllPermissionsStatus(int Rid){
		
	ArrayList<Integer> RPid=new ArrayList<Integer>();
	RPid=getAllPids(Rid);
	
		ArrayList<Permission> result = new ArrayList<>();
		try{
			String sql = "SELECT * FROM permission";
			stmt= getDbConnection().createStatement();
			rs= stmt.executeQuery(sql);
			while(rs.next()){
	            String name = rs.getString("Name");
	            String desc = rs.getString("description");
	            int id  = rs.getInt("ID");
	            if(RPid.contains(id))
	            {
	            	Permission p=new Permission(id,name,desc,"YES");
	            	result.add(p);
	            }
	            else
	            {
	            	Permission p=new Permission(id,name,desc,"NO");
	            	result.add(p);
	            }
	         }
			// get all permissions as resultset and then convert them into arrayist of permissions..
		}catch(SQLException ex){
			
		}
		
		
		return result;
		
	}
	
	
public ArrayList<Integer> getAllPids(int Rid){
		
		ArrayList<Integer> result = new ArrayList<>();
		try{
			String sql = "SELECT Permission_ID FROM Role_permission where Role_Id=?";
			ps= getDbConnection().prepareStatement(sql);
			ps.setInt(1,Rid);
		    rs=ps.executeQuery();
			while(rs.next()){
	            int id  = rs.getInt("Permission_ID");
	            result.add(id);
	         }
			// get all permissions as resultset and then convert them into arrayist of permissions..
		}catch(SQLException ex){
			
		}
		return result;
		
	}
	
public ArrayList<Permission> getAllRoles(){
		
		ArrayList<Permission> result = new ArrayList<>();
		try{
			String sql = "SELECT * FROM role";
			stmt= getDbConnection().createStatement();
			rs= stmt.executeQuery(sql);
			while(rs.next()){
	            String name = rs.getString("Name");
	            String desc = rs.getString("description");
	            int id  = rs.getInt("ID");
	            Permission p=new Permission(id,name,desc,"");
	            result.add(p);
	         }
			// get all permissions as resultset and then convert them into arrayist of permissions..
		}catch(SQLException ex){
			
		}
		
		
		return result;
		
	}

public Permission getSingleRole(int Rid){
	
	Permission result = null;
	try{
		String sql = "SELECT * FROM role WHERE ID=?";
		ps= getDbConnection().prepareStatement(sql);
		ps.setInt(1,Rid);
	    rs=ps.executeQuery();
		while(rs.next()){
			String name = rs.getString("Name");
            String desc = rs.getString("description");
            int id  = rs.getInt("ID");
            result = new Permission(id,name,desc,"");
         }
		// get all permissions as resultset and then convert them into arrayist of permissions..
	}catch(SQLException ex){
		
	}
	
	
	return result;
	
}

public ArrayList<Role_Permission> getAllRolePermission(){
	
	ArrayList<Role_Permission> result = new ArrayList<>();
	try{
		String sql = "SELECT * FROM role_permission";
		stmt= getDbConnection().createStatement();
		rs= stmt.executeQuery(sql);
		while(rs.next()){
            int roleid = rs.getInt("Role_ID");
            int perid = rs.getInt("Permission_ID");
            int id  = rs.getInt("ID");
            Role_Permission p=new Role_Permission(id,roleid,perid);
            result.add(p);
         }
		// get all permissions as resultset and then convert them into arrayist of permissions..
	}catch(SQLException ex){
		
	}
	
	
	return result;
	
}

	public void Addpermission(String pname,String pdesc)
	{
		String sql ="insert into permission (Name,description) values(?,?)";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setString(1,pname);
			ps.setString(2,pdesc);
		    ps.executeUpdate();
		} catch (SQLException e) {
		}
	}
	
	public void AddRole(String pname,String pdesc)
	{
		String sql ="insert into role (Name,description) values(?,?)";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setString(1,pname);
			ps.setString(2,pdesc);
		    ps.executeUpdate();
		} catch (SQLException e) {
		}
	}
	
	public void AddRolePermission(int roleid,int perid)
	{
		int r1=0,r2=0;
		String sql1 = "SELECT * FROM permission WHERE ID=?";
		String sql2 = "SELECT * FROM role WHERE ID=?";
		String sql ="insert ignore into Role_permission (Role_ID,Permission_ID) values(?,?)";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setInt(1,roleid);
			ps.setInt(2,perid);
		    ps.executeUpdate();
		} catch (SQLException e) {
		}
	}
	
	public void Editpermission(String pid,String pname,String pdesc)
	{
		String sql ="update permission set Name=? ,description=? where ID=?";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setString(1,pname);
			ps.setString(2,pdesc);
			ps.setString(3,pid);
		    ps.executeUpdate();
		} catch (SQLException e) {
		}
	}
	
	public void EditRole(String pid,String pname,String pdesc)
	{
		String sql ="update role set Name=? ,description=? where ID=?";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setString(1,pname);
			ps.setString(2,pdesc);
			ps.setString(3,pid);
		    ps.executeUpdate();
		} catch (SQLException e) {
		}
	}
	
	public void EditRolePermission(String id,int roleid,int perid)
	{
		String sql ="update Role_permission set Role_ID=?,Permission_ID=? where ID=?";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setInt(1,roleid);
			ps.setInt(2,perid);
			ps.setString(3,id);
		    ps.executeUpdate();
		} catch (SQLException e) {
		}
	}
	
	public void Delpermission(String pid)
	{
		String sql ="delete from permission where ID=?";
		String sql2 ="delete from Role_permission where permission_ID=?";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setString(1,pid);
		    ps.executeUpdate();
		    
		    ps= getDbConnection().prepareStatement(sql2);
			ps.setString(1,pid);
		    ps.executeUpdate();
		    
		} catch (SQLException e) {
		}
	}
	public void DelRole(String pid)
	{
		String sql ="delete from role where ID=?";
		String sql2 ="delete from Role_permission where permission_ID=?";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setString(1,pid);
		    ps.executeUpdate();
		    ps= getDbConnection().prepareStatement(sql2);
			ps.setString(1,pid);
		    ps.executeUpdate();
		} catch (SQLException e) {
		}
	}
	
	public void DelRolePermission(String id)
	{
		String sql ="delete from Role_permission where ID=?";
		try {
			ps= getDbConnection().prepareStatement(sql);
			ps.setString(1,id);
		    ps.executeUpdate();
		} catch (SQLException e) {
		}
	}
	
	private Connection getDbConnection(){
		
		if ( conn == null){
			
			try{
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://localhost/TEST", "ahmed_ashry","");
			}catch(ClassNotFoundException | SQLException ex){
				ex.printStackTrace();
			}
		}
		
		return conn;
	}

	
	
}
